package com.hcrazy.manchester.app.jdbc;

import java.util.ArrayList;
import java.util.List;

import com.hcrazy.manchester.app.pojo.PageModel;
import com.hcrazy.manchester.app.pojo.Rent;
import com.hcrazy.manchester.app.pojo.Secondary;
import com.hcrazy.manchester.app.utils.Constant;

public class SecondaryDAO extends BaseDAO<Secondary>{

	public long addSecondary(Secondary secondary) throws Exception{
		StringBuilder sb = new StringBuilder();
		sb.append("insert into him_func_secondary(");
		sb.append("sortId,images,title,description_mc,price,linkName,oldNewLevel,publishValidateDate,selledCount,selledBaseCount,");
		sb.append("phone,publishDate,remoteIp,userId,status,zipCode,address,praiseBaseCount,praiseCount,commentBaseCount,commentCount,pic) values (");
		sb.append(":sortId,:images,:title,:description_mc,:price,:linkName,:oldNewLevel,:publishValidateDate,:selledCount,:selledBaseCount,");
		sb.append(":phone,:publishDate,:remoteIp,:userId,:status,:zipCode,:address,:praiseBaseCount,:praiseCount,:commentBaseCount,:commentCount,:pic)");
		
		update(sb.toString(), secondary);
		
		return getIncreamentId();
	}
	
	public void updateSecondary(Secondary secondary) throws Exception{
		StringBuilder sb = new StringBuilder();
		sb.append("update him_func_secondary set ");
		sb.append(" sortId=:sortId,title=:title,images=:images,description_mc=:description_mc,zipCode=:zipCode,pic=:pic,");
		sb.append(" price=:price,linkName=:linkName,phone=:phone,status=:status,address=:address,updateDate=:updateDate,");
		sb.append(" oldNewLevel=:oldNewLevel,publishValidateDate=:publishValidateDate,selledCount=:selledCount,selledBaseCount=:selledBaseCount,");
		sb.append(" praiseBaseCount=:praiseBaseCount,praiseCount=:praiseCount,commentBaseCount=:commentBaseCount,commentCount=:commentCount,pic=:pic");
		sb.append(" where infoId = :infoId");
		
		update(sb.toString(), secondary);
	}
	
	public List<Secondary> listAllInfos() throws Exception{
		String sql = "select * from him_func_secondary";
		return getList(sql, Secondary.class);
	}
	
	public List<Secondary> getAllInfosBySortId(int sortId) throws Exception{
		String sql = "select a.*,b.sortName from him_func_secondary a,him_live_sort b where a.sortId = b.sortId and a.sortId = ?";
		return getList(sql, Secondary.class,sortId);
	}
	
	public PageModel getPagedInfos(PageModel model,String search,int sortId) throws Exception{
		StringBuilder querySQL = new StringBuilder("select * from (select a.*,b.sortName from him_func_secondary a,him_live_sort b where a.sortId = b.sortId) as c");
		StringBuilder countSQL = new StringBuilder("select count(*) from (select a.*,b.sortName from him_func_secondary a,him_live_sort b where a.sortId = b.sortId) as c ");
		StringBuilder whereSQL = new StringBuilder(" where (title like ?  or phone like ? or linkName like ?) ");
		List<Object> paramList = new ArrayList<Object>();
		paramList.add("%"+search+"%");
		paramList.add("%"+search+"%");
		paramList.add("%"+search+"%");
		if(sortId > 0){
			whereSQL.append(" and sortId = ?");
			paramList.add(sortId);
		}
		return this.getPageModel(model, querySQL, countSQL, whereSQL, paramList, Secondary.class);
	}
	
	public PageModel getPagedInfos(PageModel model, int sortId, int status,int priceRange,int priceStart, int priceEnd,
			String keywords,long userId) throws Exception {
		StringBuilder querySQL = new StringBuilder("select * from (select a.*,b.sortCode,b.sortName from him_func_secondary a,him_live_sort b where a.sortId = b.sortId) c ");
		StringBuilder countSQL = new StringBuilder("select count(*) from (select a.*,b.sortCode from him_func_secondary a,him_live_sort b where a.sortId = b.sortId) c ");
		StringBuilder whereSQL = new StringBuilder(" where title like ? and description_mc like ? ");
		List<Object> paramList = new ArrayList<Object>();
		paramList.add("%" + keywords + "%");
		paramList.add("%" + keywords + "%");
		
		if (sortId > 0) {
			whereSQL.append(" and (sortId = ? or sortCode like ?)");
			paramList.add(sortId);
			paramList.add("%:" + sortId + ":%");
		}
		if(status > 0){
			whereSQL.append(" and status = ?");
			paramList.add(status);
		}
		if(priceRange > 0){
			switch(priceRange){
				case Constant.SECON_PRICE_1:
					whereSQL.append(" and price <= 100");
					break;
				case Constant.SECON_PRICE_2:
					whereSQL.append(" and price >= 100 and price <= 200");
					break;
				case Constant.SECON_PRICE_3:
					whereSQL.append(" and price >= 200 and price <= 500");
					break;
				case Constant.SECON_PRICE_4:
					whereSQL.append(" and price >= 500 and price <= 1000");
					break;
				case Constant.SECON_PRICE_5:
					whereSQL.append(" and price >= 1000 and price <= 2000");
					break;
				case Constant.SECON_PRICE_6:
					whereSQL.append(" and price >= 2000 and price <= 3500");
					break;
				case Constant.SECON_PRICE_7:
					whereSQL.append(" and price >= 3500");
					break;
				
			}
		}
		if(priceStart >= 0 && priceEnd > 0){
			whereSQL.append(" and price >= ? and price <= ?");
			paramList.add(priceStart);
			paramList.add(priceEnd);
		}
		if(userId > 0){
			whereSQL.append(" and userId = ?");
			paramList.add(userId);
		}
		System.out.println(querySQL.toString());
		return this.getPageModel(model, querySQL, countSQL, whereSQL, paramList, Secondary.class);
	}
	
	public Secondary getById(long infoId) throws Exception{
		String sql = "select * from him_func_secondary where infoId = ?";
		return this.getJavaBean(sql, Secondary.class, infoId);
	}
	
	public void delSecondary(long infoId) throws Exception{
		String sql = "delete from him_func_secondary where infoId = ?";
		this.commonUpdate(sql, infoId);
	}
	
	public void setViews(long views,long id) throws Exception{
		String sql = "update him_func_secondary set views = ? where infoId = ?";
		this.commonUpdate(sql, views,id);
	}
	
	public PageModel getCollegeSecondaries(long userId,int moduleType,PageModel model) throws Exception{
		String sql = "select a.type,b.* from him_user_collection a,him_func_secondary b where a.infoId = b.infoId";
		StringBuilder querySQL = new StringBuilder("select * from ("+sql+") as m");
		StringBuilder countSQL = new StringBuilder("select count(*) from ("+sql+") as m");
		StringBuilder whereSQL = new StringBuilder(" where userId = ? and type = ?");
		
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(userId);
		paramList.add(moduleType);
		
		return this.getPageModel(model, querySQL, countSQL, whereSQL, paramList, Secondary.class);
	}
	
	public long getCommentCount(long infoId) throws Exception{
		String sql = "select commentCount from him_func_secondary where infoId = ?";
		return this.getJdbcTemplate().queryForLong(sql,infoId);
	}
	
	public void addCommentCount(long infoId) throws Exception {
		String sql = "update him_func_secondary set commentCount = commentCount + 1 where infoId = ?";
		this.commonUpdate(sql, infoId);
	}
	
	public long getPraiseCount(long infoId) throws Exception{
		String sql = "select praiseCount from him_func_secondary where infoId = ?";
		return this.getJdbcTemplate().queryForLong(sql,infoId);
	}
	
	public void addPraiseCount(long infoId) throws Exception {
		String sql = "update him_func_secondary set praiseCount = praiseCount + 1 where infoId = ?";
		this.commonUpdate(sql, infoId);
	}
	
	public long getSelledCount(long infoId) throws Exception{
		String sql = "select selledCount from him_func_secondary where infoId = ?";
		return this.getJdbcTemplate().queryForLong(sql,infoId);
	}
	
	public void addselledCount(long infoId) throws Exception {
		String sql = "update him_func_secondary set selledCount = selledCount + 1 where infoId = ?";
		this.commonUpdate(sql, infoId);
	}
	
	public List<Secondary> getSecondaries(int size) throws Exception{
		String sql = "select * from him_func_secondary order by publishDate desc limit ?";
		
		return this.getList(sql, Secondary.class,size);
	}
}
